#!/usr/bin/python
# -*- coding: UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
export data from mysql

Authors: xiaojianghui
Date:    10/22/2020 11:01 AM
"""

import time
import xlwt
from dbConnecttion.MysqlConn import Mysql
from common import times


def cur_date():
    """
    current date
    :return createTime: string
    """
    create_time = time.strftime("%Y-%m-%d", time.localtime())
    return create_time


def get_results(status):
    """
    Execute mysql
    :param status: int
    :return: list
    """
    mysql = Mysql()
    last_month = times.last_month_date()
    sql = "select * from cve_issue_create_record where status = %s and create_time > %s"
    val = (status, last_month)
    results = mysql.getMany(sql, val)
    print(results)
    mysql.dispose()
    mysql.close()
    return results


def update_status(status):
    """
    Execute mysql
    :param status: int
    :return: list
    """
    mysql = Mysql()
    sql = 'update cve_issue_create_record set status = %s,update_time = %s where status = %s'
    val = (4, time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), status)
    mysql.update(sql, val)
    mysql.dispose()
    mysql.close()


def generate_excels(status, path):
    """
    export excel
    :param status:
    :param path: int, string
    :return: None
    """
    results = get_results(status)
    if not results:
        print("无issue记录")
        return False
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('sheet1', cell_overwrite_ok=True)
    # Column field
    column_names = ['id', 'cve_id', 'cve_num', 'cve_desc', 'cve_level',
                    'cve_version', 'repair_time', 'pack_name',
                    'nvd_score', 'n_vector_value', 'create_time', 'update_time',
                    'delete_time', 'status', 'error_description']
    # Write the first row, column name
    for i in range(0, len(column_names)):
        sheet1.write(0, i, column_names[i])
    # Write multiple lines
    num = 0
    for i in results:
        sheet1.write(num + 1, 0, i["id"])
        sheet1.write(num + 1, 1, i["cve_id"])
        sheet1.write(num + 1, 2, i["cve_num"])
        sheet1.write(num + 1, 3, i["cve_desc"])
        sheet1.write(num + 1, 4, i["cve_level"])
        sheet1.write(num + 1, 5, i["cve_version"])
        sheet1.write(num + 1, 6, i["repair_time"])
        sheet1.write(num + 1, 7, i["pack_name"])
        sheet1.write(num + 1, 8, i["nvd_score"])
        sheet1.write(num + 1, 9, i["n_vector_value"])
        sheet1.write(num + 1, 10, i['create_time'])
        sheet1.write(num + 1, 11, i["update_time"])
        sheet1.write(num + 1, 12, i["delete_time"])
        sheet1.write(num + 1, 13, i['status'])
        sheet1.write(num + 1, 14, i['error_description'])
        num += 1
    # save document
    f.save(path + '_' + times.last_month_date()+'_' + cur_date() + '.xls')
    update_status(status)
